package org.svnadmin.dao;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.svnadmin.constant.Constants;
import org.svnadmin.entity.PjAuth;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 项目资源的权限DAO
 *
 * @author <a href="mailto:yuanhuiwu@gmail.com">Huiwu Yuan</a>
 * @since 1.0
 */
@Repository(PjAuthDao.BEAN_NAME)
public class PjAuthDao extends Dao {
    /**
     * Bean名称
     */
    public static final String BEAN_NAME = "pjAuthDao";

    /**
     * @param pj  项目
     * @param gr  组
     * @param res 资源
     * @return 项目组资源的权限
     */
    public PjAuth getByGr(String pj, String gr, String res) {
        String sql = "select pj,res,rw,gr,' ' usr,' ' usrname from pj_gr_auth where pj = ? and gr=? and res=? ";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);
            pstmt.setString(index++, res);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return readPjAuth(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
        return null;
    }

    /**
     * @param pj  项目
     * @param usr 用户
     * @param res 资源
     * @return 项目用户资源的权限
     */
    public PjAuth getByUsr(String pj, String usr, String res) {
        String sql = "select a.pj,a.res,a.rw,b.usr,b.name as usrname,' ' gr from pj_usr_auth a left join usr b on (a.usr=b.usr) where a.pj = ? and a.usr=? and a.res=? ";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, usr);
            pstmt.setString(index++, res);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return readPjAuth(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
        return null;
    }

    /**
     * @param usr 用户
     * @return 用户的权限
     */
    public List<PjAuth> getByUsr(String usr) {
        String sql = "select b.pj,p.des,b.usr,b.res,b.rw from usr a";
        sql += " join pj_usr_auth b on (a.usr = b.usr)";
        sql += " join pj p on (b.pj=p.pj)";
        sql += " where a.usr=?";

        sql += " union all";

        sql += " select c.pj,p.des,a.usr,c.res,c.rw from usr a";
        sql += " join pj_gr_usr b on (a.usr = b.usr)";
        sql += " join pj_gr_auth c on (b.pj = c.pj and b.gr = c.gr)";
        sql += " join pj p on (b.pj=p.pj)";
        sql += " where a.usr=?";

        sql += " order by 1,4";// TODO 为了兼容sqlserver

        List<PjAuth> list = new ArrayList<PjAuth>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, usr);
            pstmt.setString(index++, usr);

            rs = pstmt.executeQuery();
            while (rs.next()) {

                PjAuth result = new PjAuth();
                result.setPj(rs.getString("pj"));
                result.setDes(rs.getString("des"));
                result.setUsr(rs.getString("usr"));
                result.setRes(rs.getString("res"));
                String rw = rs.getString("rw");
                if (StringUtils.isBlank(rw)) {
                    rw = "";
                }
                result.setRw(rw);

                list.add(result);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param pj  项目
     * @param res 资源
     * @return 项目资源的权限列表
     */
    public List<PjAuth> getList(String pj, String res) {
        String sql = "select pj,res,rw,gr,' ' usr,' ' usrname from pj_gr_auth where pj=? and res = ? " + " UNION "
                + " select a.pj,a.res,a.rw,' ' gr,a.usr,b.name as usrname from pj_usr_auth a left join usr b on (a.usr=b.usr) where a.pj=? and a.res = ? "
                + " order by res,gr,usr";
        List<PjAuth> list = new ArrayList<PjAuth>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, res);
            pstmt.setString(index++, pj);
            pstmt.setString(index++, res);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPjAuth(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param pj 项目
     * @return 项目资源的权限列表
     */
    public List<PjAuth> getList(String pj) {
        String sql = "select pj,res,rw,gr,' ' usr,' ' usrname from pj_gr_auth where pj=? " + " UNION "
                + " select a.pj,a.res,a.rw,' ' gr,a.usr,b.name as usrname from pj_usr_auth a left join usr b on (a.usr = b.usr) where a.pj=? "
                + " order by res,gr,usr";
        List<PjAuth> list = new ArrayList<PjAuth>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, pj);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPjAuth(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param rootPath svn root path
     * @return 具有相同svn root的项目资源的权限列表
     */
    public List<PjAuth> getListByRootPath(String rootPath) {
        String sql = "select pj,res,rw,gr,' ' usr,' ' usrname from pj_gr_auth where pj in (select distinct pj from pj where type=? and path like ?) "
                + " UNION "
                + " select a.pj,a.res,a.rw,' ' gr,a.usr,b.name usrname from pj_usr_auth a left join usr b on (a.usr=b.usr) where a.pj in (select distinct pj from pj where type=? and path like ?) "
                + " order by res,gr,usr";
        List<PjAuth> list = new ArrayList<PjAuth>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, Constants.HTTP_MUTIL);
            pstmt.setString(index++, rootPath + "%");// TODO 大小写敏感?
            pstmt.setString(index++, Constants.HTTP_MUTIL);
            pstmt.setString(index++, rootPath + "%");// TODO 大小写敏感?

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(readPjAuth(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }
    }

    /**
     * @param rs ResultSet
     * @return 项目资源的权限
     * @throws SQLException jdbc异常
     */
    PjAuth readPjAuth(ResultSet rs) throws SQLException {
        PjAuth result = new PjAuth();
        result.setPj(rs.getString("pj"));
        result.setGr(rs.getString("gr"));
        result.setUsr(rs.getString("usr"));
        result.setUsrName(rs.getString("usrname"));
        result.setRes(rs.getString("res"));
        String rw = rs.getString("rw");
        if (StringUtils.isBlank(rw)) {
            rw = "";
        }
        result.setRw(rw);

        return result;
    }

    /**
     * 删除项目 组资源的权限
     *
     * @param pj  项目
     * @param gr  组
     * @param res 资源
     */
    public void deleteByGr(String pj, String gr, String res) {
        String sql = "delete from pj_gr_auth where pj = ? and gr=? and res=? ";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);
            pstmt.setString(index++, res);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除项目用户资源的权限
     *
     * @param pj  项目
     * @param usr 用户
     * @param res 资源
     */
    public void deleteByUsr(String pj, String usr, String res) {
        String sql = "delete from pj_usr_auth where pj = ? and usr=? and res=? ";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, usr);
            pstmt.setString(index++, res);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除项目 资源的权限
     *
     * @param pj 项目
     */
    public void deletePj(String pj) {
        // pj_gr_auth
        String sql = "delete from pj_gr_auth where pj = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
        // pj_usr_auth
        sql = "delete from pj_usr_auth where pj = ?";
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除项目 组资源的权限
     *
     * @param pj 项目
     * @param gr 组
     */
    public void deletePjGr(String pj, String gr) {
        String sql = "delete from pj_gr_auth where pj = ? and gr=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, gr);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 删除用户的项目资源的权限
     *
     * @param usr 用户
     */
    public void deleteUsr(String usr) {
        String sql = "delete from pj_usr_auth where usr=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, usr);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(null, pstmt, conn);
        }
    }

    /**
     * 保存项目组权限
     *
     * @param pjAuth 项目组权限
     */
    public void saveByGr(PjAuth pjAuth) {
        if (this.getByGr(pjAuth.getPj(), pjAuth.getGr(), pjAuth.getRes()) == null) {
            String sql = "insert into pj_gr_auth (pj,gr,res,rw) values (?,?,?,?)";

            Connection conn = null;
            PreparedStatement pstmt = null;
            try {
                conn = this.getConnection();
                pstmt = conn.prepareStatement(sql);
                int index = 1;
                pstmt.setString(index++, pjAuth.getPj());
                pstmt.setString(index++, pjAuth.getGr());
                pstmt.setString(index++, pjAuth.getRes());
                pstmt.setString(index++, pjAuth.getRw());

                pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                this.close(null, pstmt, conn);
            }
        } else {
            String sql = "update pj_gr_auth set rw=? where pj=? and gr=? and res=?";

            Connection conn = null;
            PreparedStatement pstmt = null;
            try {
                conn = this.getConnection();
                pstmt = conn.prepareStatement(sql);
                int index = 1;
                pstmt.setString(index++, pjAuth.getRw());
                pstmt.setString(index++, pjAuth.getPj());
                pstmt.setString(index++, pjAuth.getGr());
                pstmt.setString(index++, pjAuth.getRes());

                pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                this.close(null, pstmt, conn);
            }
        }
    }

    /**
     * 保存项目用户权限
     *
     * @param pjAuth 项目用户权限
     */
    public void saveByUsr(PjAuth pjAuth) {
        if (this.getByUsr(pjAuth.getPj(), pjAuth.getUsr(), pjAuth.getRes()) == null) {
            String sql = "insert into pj_usr_auth (pj,usr,res,rw) values (?,?,?,?)";

            Connection conn = null;
            PreparedStatement pstmt = null;
            try {
                conn = this.getConnection();
                pstmt = conn.prepareStatement(sql);
                int index = 1;
                pstmt.setString(index++, pjAuth.getPj());
                pstmt.setString(index++, pjAuth.getUsr());
                pstmt.setString(index++, pjAuth.getRes());
                pstmt.setString(index++, pjAuth.getRw());

                pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                this.close(null, pstmt, conn);
            }
        } else {
            String sql = "update pj_usr_auth set rw=? where pj=? and usr=? and res=?";

            Connection conn = null;
            PreparedStatement pstmt = null;
            try {
                conn = this.getConnection();
                pstmt = conn.prepareStatement(sql);
                int index = 1;
                pstmt.setString(index++, pjAuth.getRw());
                pstmt.setString(index++, pjAuth.getPj());
                pstmt.setString(index++, pjAuth.getUsr());
                pstmt.setString(index++, pjAuth.getRes());

                pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                this.close(null, pstmt, conn);
            }
        }
    }

    /**
     * @param pj 项目
     * @return 项目的资源列表
     */
    public List<String> getResList(String pj) {
        String sql = "select distinct res from pj_gr_auth where pj=? "
                + " UNION select distinct res from pj_usr_auth where pj=? order by res";

        List<String> list = new ArrayList<String>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            int index = 1;
            pstmt.setString(index++, pj);
            pstmt.setString(index++, pj);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(rs.getString("res"));

            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            this.close(rs, pstmt, conn);
        }

    }

    /**
     * 从模板库复制权限
     */
    public void copydemo(String demo, String pj) {
        List<PjAuth> list = new ArrayList<PjAuth>();
        list = getList(demo);
        for (int i = 0; i < list.size(); i++) {
            PjAuth pjAuth = list.get(i);
            pjAuth.setPj(pj);
            if ("".equals(pjAuth.getUsr().trim())) {
                saveByGr(pjAuth);
            } else {
                saveByUsr(pjAuth);
            }
        }
    }
}